BigQueryのテーブル作成実践(データ定義言語(DDL))
Google BigQueryでは、テーブル作成を行う術が複数提供されています。当エントリではその中から「データ定義言語(DDL)」による作成方法について、実践を交えて見ていきたいと思います。
目次
構文
DDLによるテーブル作成(CREATE TABLE)の構文は以下の通り。
table_name
[(
column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]
要点
上記ドキュメントに記載の内容について、ザッと目を通した上で気になるところをまとめてみました。
テーブル作成の際に取れる"挙動"としては何パターンか存在。
- 新規で作成(CREATE TABLE)
- 既存テーブルから新しいテーブルを作成(CREATE TABLE ... AS SELECT)
- テーブルが存在していなければ作成(CREATE TABLE IF NOT EXISTS)
- テーブルが存在していればそれを上書きして作成(CREATE OR REPLACE TABLE)
データ型について：詳細は下記公式ドキュメントを参照。
BigQueryで利用出来るテーブルカラムの「データ型」は以下の通り。
- STRING(文字列型)
- BYTES(可変長文字バイナリデータ)
- INTEGER(整数型)
- FLOAT(浮動小数点型)
- NUMERIC(10 進 38 桁の精度と 10 進 9 桁の尺度の正確な数値)
- BOOLEAN(ブール型)
- TIMESTAMP(タイムスタンプ型 ※マイクロ秒の精度で、絶対的な時刻)
- DATE(日付型)
- TIME(時刻型 ※特定の日付とは関係無し)
- DATETIME(日時型 ※年、月、日、時、分、秒、およびサブ秒)
- GEOGRAPHY(地理型)
- RECORD(構造体型/STRUCT)
カラムの属性としてNULLABLE, REQUIRED(NOT NULL), REPEATEDが指定出来る。
BigQuery では、列に対して次のモードがサポートされます。
モードは省略可能です。モードが指定されていない場合、
列はデフォルトの NULLABLE に設定されます。NULLABLE: 列で NULL 値が許可されます（デフォルト）。
REQUIRED(必須): NULL 値は許可されません。
REPEATED(反復): 列に指定された型の値の配列が含まれます
モード - スキーマの指定 | BigQuery | Google Cloud
カラムのコメントはcolumn_schema内でdescriptionを使う事によって指定可能(テーブルのコメントは後述の table_option_listで指定可能)
文字列型に桁数制限は無い(!)、その代わり行の最大サイズに制限がある(100MB)のでこれを超えるとアウト。
行の最大サイズ - 100 MB
行のサイズは行データの内部表現に基づくことから、その最大サイズに対する上限は概算値です。
行の最大サイズに対する上限は、クエリジョブ実行の特定の段階で適用されます。
割り当てと上限 | BigQuery | Google Cloud
パーティションという概念がある(PARTITION BYで指定)。指定無し/取り込み時間で指定/条件を満たすデータ型のカラムで指定、が選べる
クラスターという概念がある(CLUSTER BYで指定)
オプションリスト(table_option_list)を使用することで、ラベルや有効期限などのテーブル オプションを設定する事が可能となっている
- expiration_timestamp(TIMESTAMP)：テーブルの有効期限
- partition_expiration_days(FLOAT64)：パーティションの有効期限
- require_partition_filter(BOOL)：パーティションフィルタの設定有無
- kms_key_name(STRING)：暗号化に用いるKMS名称
- friendly_name(STRING)：テーブル名コメント？
- description(STRING)：テーブル名コメント
- labels(ARRAY
CREATE TABLE実践
コンソール経由で試す
まずは一番手っ取り早いコンソール経由から試してみます。任意のデータセットから「テーブルを作成」を選択。
テーブルの作成元「空のテーブル」を指定、テーブル名も任意の内容を入力。
必要な個数分、「フィールドを追加」を押下してカラムの設定行を追加していきます。ここで指定可能な内容は「名前」「型」「モード」の3種のみ。
パーティションとクラスタの設定も以下のように個別指定可能です。「テーブルを作成」を押下。
テーブルが作成出来ました。「スキーマを編集」ボタンでカラム定義の編集が行えます。
この編集画面では、モードの一部編集、及び説明の編集が可能となっています。
編集後の画面。
また、「詳細」タブについては以下のような表示内容となっています。「説明」と「ラベル」が付与可能なのはデータセットと同様です。
テーブルの属性として変更可能なのは「有効期限」のみとなっています。
テーブルのスキーマ編集については、プルダウンを用いた上記の手順とは異なり、テキストとして編集した内容を定義として用いる事も可能です。(※この場合、NULLABLE等の指定ってどうやるんだろう？)
CLI経由(bq query)で試す
上記で作成した内容を、同じ様な定義でCLI経由(SQLとしてのCREATE TABLE文を使用)して実行してみたものが以下となります。bq queryコマンドを使ってCREATE TABLE文を実行した形です。基本的には設定に倣う形でDDLは用意出来たのですが、一部REPEATEDを指定する方法が分からずでした(この辺そもそも使い勝手とかあまり把握していないのもあるので、別途エントリを改める形で深堀りしてみたいと思います)
$ bq query --nouse_legacy_sql '
CREATE TABLE cmbqdataset.bqtable_from_cli (
/** 列毎の定義 */
user_id INT64 NOT NULL OPTIONS(description="ユーザーID、一意のキー"),
user_name STRING NOT NULL OPTIONS(description="ユーザー名"),
item_bytes BYTES,
value_float FLOAT64,
value_numeric NUMERIC,
value_boolean BOOLEAN,
birthday DATE NOT NULL OPTIONS(description="誕生日"),
create_timestamp TIMESTAMP,
create_datetime DATETIME,
create_time TIME,
item_geography GEOGRAPHY,
item_record STRUCT <
key STRING
>
)
/** パーティション指定 */
PARTITION BY DATE(create_timestamp)
/** クラスタ指定 */
CLUSTER BY user_id
/** その他オプション指定 */
OPTIONS(
expiration_timestamp=TIMESTAMP "2020-12-31 23:59:59 UTC",
partition_expiration_days=7,
description="a table that expires at 2021, with each partition living for 7 days",
labels=[("production_mode", "false")]
)
'
Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... (0s) Current status: DONE
Created cm-xxxxxxxxxxxxx.cmbqdataset.bqtable_from_cli
クエリ実行後のコンソールで確認した内容がこちら(カラム定義)。
テーブルに関する属性周りの設定。
まとめ
という訳で、BigQueryのテーブル作成をDDL経由で実践してみた内容のまとめでした。
基本的には従来のデータベースで実践するような構文と然程変わらないイメージでしたが、やはり細かいところで"BigQueryならでは"の部分が顔を出して来ていました。この辺、他のサービスとの『違い』を整理しつつ、使い分けて行きたいと思います。
